﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;

namespace SMM
{
    class sqlHelper
    {
        string connectString = "Data Source=.;Initial Catalog=supermarket;Integrated Security=True";
        SqlConnection sqlCnt = null;
        public bool linkDatabase()
        {
            try
            {
                sqlCnt = new SqlConnection(connectString);
                sqlCnt.Open();
                return true;
            }
            catch (Exception e)
            {
                return false;
            }
            finally {
                sqlCnt.Close();
            }
      
        }

        public bool login(String ID,String pwd) {
            sqlCnt = new SqlConnection(connectString);
            sqlCnt.Open();
            SqlCommand command = new SqlCommand();
            command.Connection = sqlCnt;
            command.CommandType = CommandType.Text;
            command.CommandText = "select * from login where ID='"+ID+"' AND Pwd='"+pwd+"'";
            SqlDataReader reader = command.ExecuteReader();     //执行SQL，返回一个“流”
            while (reader.Read())
            {
                sqlCnt.Close();
                return true;
            }
            sqlCnt.Close();
            return false;
        }

        public String getUserNameByID(String ID) {
            sqlCnt = new SqlConnection(connectString);
            sqlCnt.Open();
            SqlCommand command = new SqlCommand();
            command.Connection = sqlCnt;
            command.CommandType = CommandType.Text;
            command.CommandText = "select Name from cashier where cashierID='" + ID + "'";
            SqlDataReader reader = command.ExecuteReader();
            reader.Read();
            //sqlCnt.Close();
            return reader[0]+"";

        }

        public bool cargoExist(String bar) {
            sqlCnt = new SqlConnection(connectString);
            sqlCnt.Open();
            SqlCommand command = new SqlCommand();
            command.Connection = sqlCnt;
            command.CommandType = CommandType.Text;
            command.CommandText = "select * from cargo where Barcode='"+bar+"'";
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                sqlCnt.Close();
                return true;
            }
            sqlCnt.Close();
            return false;
        }

        public String getCargoInfoByBar(String bar, String name) {
            sqlCnt = new SqlConnection(connectString);
            sqlCnt.Open();
            SqlCommand command = new SqlCommand();
            command.Connection = sqlCnt;
            command.CommandType = CommandType.Text;
            command.CommandText = "select "+name+" from cargo where Barcode='" + bar + "'";
            SqlDataReader reader = command.ExecuteReader();
            reader.Read();
            //sqlCnt.Close();
            return reader[0] + "";
        }

        public DataSet getTable(String table1,String table2) {
            DataSet ds = new DataSet();
            try {
                sqlCnt = new SqlConnection(connectString);
                sqlCnt.Open();
                string sql = "select * from "+ table1 + " UNION select * from "+ table2 + "";
                SqlDataAdapter sda = new SqlDataAdapter(sql, sqlCnt);
                sda.Fill(ds);
            }
            catch (SqlException sqle) {

            }
            finally {
                sqlCnt.Close();
            }
            return ds;
        }

        public DataSet getTable(String table1)
        {
            DataSet ds = new DataSet();
            try
            {
                sqlCnt = new SqlConnection(connectString);
                sqlCnt.Open();
                string sql = "select * from " + table1 + "";
                SqlDataAdapter sda = new SqlDataAdapter(sql, sqlCnt);
                sda.Fill(ds);
            }
            catch (SqlException sqle)
            {

            }
            finally
            {
                sqlCnt.Close();
            }
            return ds;
        }

        public bool updateMen(String table, String id, String name, int state) {
            String sql = "update ";
            if ("cashier".Equals(table)) {
                sql += table + " set Name='" + name + "', State='" + state + "' where cashierID='" + id + "'";
            }
            else {
                sql += table + " set Name='" + name + "', State='" + state + "' where cargoclerkID='" + id + "'";
            }
            try {
                sqlCnt = new SqlConnection(connectString);
                sqlCnt.Open();
                SqlCommand cmd_update = new SqlCommand(sql, sqlCnt);
                cmd_update.ExecuteNonQuery();
               // Console.WriteLine(res_2);
                return true;
            } catch (SqlException sqle) {
                return false;
            }

           
        }

        public bool updateCargo(String id,String bar,String name,String type,float price,int stock,int minStock) {
            String sql = "update cargo set Barcode='"+bar+"',Name='"+name+"',Type='"+type
                +"',Price="+price+",Stock="+stock+",MinStock="+minStock+" where cargoID='"+id+"'";
            try
            {
                sqlCnt = new SqlConnection(connectString);
                sqlCnt.Open();
                SqlCommand cmd_update = new SqlCommand(sql, sqlCnt);
                cmd_update.ExecuteNonQuery();
                return true;
            }
            catch (SqlException sqle)
            {
                return false;
            }
        }

        public DataSet getTable() {
            DataSet ds = new DataSet();
            try
            {
                sqlCnt = new SqlConnection(connectString);
                sqlCnt.Open();
                string sql = "select * from cargo where Stock<=MinStock";
                SqlDataAdapter sda = new SqlDataAdapter(sql, sqlCnt);
                sda.Fill(ds);
            }
            catch (SqlException sqle){ }
            finally
            {
                sqlCnt.Close();
            }
            return ds;
        }

        public bool addCargoNum(String bar,int num) {
            String sql = "update cargo set Stock=Stock+"+num+" where Barcode='"+bar+"'";
            try
            {
                sqlCnt = new SqlConnection(connectString);
                sqlCnt.Open();
                SqlCommand cmd_update = new SqlCommand(sql, sqlCnt);
                cmd_update.ExecuteNonQuery();
                return true;
            }
            catch (SqlException sqle)
            {
                return false;
            }
        }
    }
}
